from sqlalchemy import create_engine, text
from datetime import datetime, timedelta
import time
import pandas as pd
import os
import json
import numpy as np
import pandas as pd
import re
from tqdm import tqdm
engine = create_engine(
   'mysql+mysqlconnector://root:Bz_202501@bj-cdb-ckq2r8ro.sql.tencentcdb.com:25622/bz_system',
    pool_recycle=3600,
    echo=False,  # 将echo从True改为False以关闭SQL日志
    isolation_level="READ COMMITTED",
    pool_pre_ping=True
)
# 定义表名映射
date_flag_mapping = {
    'sle_bioage': 'endDate',
    'daily_summary': 'startDate',
    'hrv_daily': 'startDate',
    'pwf_daily': 'startDate',
    'mental_daily':'startDate',
    'sleep_noon_to_noon':'startDate',
    'near_real_time':'endDate',
    'fitness_event':'startDate',
    'hrv_continuous':'startDate',
    'mental_hourly':'startDate',
    'pwf_continuous':'startDate',
    'sleep_episodes':'startTime',
}

def read_all_folder_count():
    """
    从数据库中读取 folder_count 表的所有数据，返回 DataFrame
    """
    table = 'folder_count'
    query = f"SELECT * FROM {table}"
    try:
        df = pd.read_sql(query, engine)
        #print(f"已读取 {len(df)} 条数据。")
        return df
    except Exception as e:
        #print(f"从数据库读取 folder_count 数据失败: {e}")
        return pd.DataFrame()  # 返回空DataFrame作为错误处理
    
def analyse1(df):
    df = df[(df['ott_analysis'] != 0) & (df['near_real_time'] != 0)]
    df = df.groupby('profile_id').filter(lambda x: len(x) > 2)

    folder_cols = [
        'sle_bioage', 'daily_summary', 'hrv_daily', 'pwf_daily', 'mental_daily',
        'sleep_noon_to_noon', 'near_real_time', 'fitness_event', 'hrv_continuous',
        'ott_analysis_night', 'near_real_time_night', 'mental_hourly',
        'pwf_continuous', 'sleep_episodes', 'ott_analysis'
    ]
    result = []
    for col in folder_cols:
        if col in ['fitness_event', 'ott_analysis', 'near_real_time']:
            continue
        if col in df.columns:
            zero_ratio = (df[col] == 0).sum() / len(df) if len(df) > 0 else 0
            result.append({'folder': col, 'missing_ratio': f"{zero_ratio:.2%}"})
    print(pd.DataFrame(result).to_string(index=False))

def analyse2(df):
    df = df[(df['ott_analysis_night'] != 0) & (df['near_real_time_night'] != 0)]
    df = df.groupby('profile_id').filter(lambda x: len(x) > 2)

    folder_cols = [
        'sle_bioage', 'daily_summary', 'hrv_daily', 'pwf_daily', 'mental_daily',
        'sleep_noon_to_noon', 'near_real_time', 'fitness_event', 'hrv_continuous',
        'ott_analysis_night', 'near_real_time_night', 'mental_hourly',
        'pwf_continuous', 'sleep_episodes', 'ott_analysis'
    ]
    result = []
    for col in folder_cols:
        if col in ['fitness_event', 'ott_analysis', 'near_real_time', 'near_real_time_night', 'ott_analysis_night']:
            continue
        if col in df.columns:
            zero_ratio = (df[col] == 0).sum() / len(df) if len(df) > 0 else 0
            result.append({'folder': col, 'missing_ratio': f"{zero_ratio:.2%}"})
    print(pd.DataFrame(result).to_string(index=False))
if __name__ == "__main__":
    # 读取 folder_count 表数据
    df = read_all_folder_count()

    df = df[(df['date_flag'].astype(str) == '2025-05-29') | 
        (df['date_flag'].astype(str) == '2025-05-30') | 
        (df['date_flag'].astype(str) == '2025-05-31') | 
        (df['date_flag'].astype(str) == '2025-06-01') | 
        (df['date_flag'].astype(str) == '2025-06-02') | 
        (df['date_flag'].astype(str) == '2025-06-03')]
    #print(df['date_flag'].unique())
    analyse1(df)
    analyse2(df)